drop table if exists jms_dwd.dwd_waybill_order_index_dt  ;
create external table jms_dwd.dwd_waybill_order_index_dt (
waybill_no                     string            comment      '运单号'
,waybill_status_code           bigint            comment      '运单状态code'
,goods_type_id                 bigint            comment      '物品类型id'
,goods_type_code               string            comment      '物品类型code'
,goods_type_name               string            comment      '物品类型名称'
,goods_name                    string            comment      '物品名称'
,package_length                decimal(14,2)     comment      '包裹总长单位厘米'
,package_wide                  decimal(14,2)     comment      '包裹总宽单位厘米'
,package_high                  decimal(14,2)     comment      '包裹总高单位厘米'
,package_volume                decimal(14,2)     comment      '包裹体积重单位千克'
,package_total_weight          decimal(14,2)     comment      '包裹总重量单位: 千克'
,package_total_volume          decimal(14,2)     comment      '包裹总体积单位立方厘米'
,package_charge_weight         decimal(14,2)     comment      '包裹计费重量单位千克'
,box_standard_code             string            comment      '箱子规格code'
,box_standard_name             string            comment      '箱子规格名称'
,collect_staff_code            string            comment      '揽件业务员code'
,collect_staff_name            string            comment      '揽件业务员名称'
,collect_time                  timestamp         comment      '揽件时间'
,dispatch_staff_code           string            comment      '派件业务员code'
,dispatch_staff_name           string            comment      '派件业务员名称'
,dispatch_time                 timestamp         comment      '派件时间'
,dispatch_network_code         string            comment      '派件网点code'
,dispatch_network_name         string            comment      '派件网点名称'
,dispatch_franchisee_code      string            comment      '派件网点加盟商code'
,dispatch_franchisee_name      string            comment      '派件网点加盟商名称'
,dispatch_agent_code           string            comment      '派件网点代理区code'
,dispatch_agent_name           string            comment      '派件网点代理区名称'
,exception_code                string            comment      '异常原因编码'
,exception_reason              string            comment      ''
,terminal_dispatch_code        string            comment      '三段码'
,first_code                    string            comment      '一段码'
,second_code                   string            comment      '二段码'
,third_code                    string            comment      '三段码'
,is_sign                       smallint          comment      '签收标识1是，0否'
,sign_network_code             string            comment      '签收网点code'
,sign_network_name             string            comment      '签收网点名称'
,sign_franchisee_code          string            comment      '签收网点加盟商code'
,sign_franchisee_name          string            comment      '签收网点加盟商名称'
,sign_agent_code               string            comment      '签收网点代理区code'
,sign_agent_name               string            comment      '签收网点代理区名称'
,sign_finance_code             string            comment      '签收财务网点编码'
,sign_time                     timestamp         comment      '签收时间'
,is_abnormal                   smallint          comment      '问题件标识1是，0否'
,abnormal_reg_network_code     string            comment      '问题件登记网点code'
,abnormal_reg_time             timestamp         comment      '问题件登记时间'
,waybill_source_code           string            comment      '运单来源code'
,waybill_source_name           string            comment      '运单来源名称'
,send_code                     string            comment      '寄件方式code'
,send_name                     string            comment      '寄件方式名称'
,dispatch_code                 string            comment      '派件方式code'
,dispatch_name                 string            comment      '派件方式名称'
,is_void                       smallint          comment      '是否作废件1是,0否'
,is_refund                     smallint          comment      '是否退件1是,0否'
,is_need_receipt               smallint          comment      '是否需要需要签回单1是,0否'
,receipt_no                    string            comment      '回单编号'
,customer_code                 string            comment      '客户编号code'
,customer_name                 string            comment      '客户编号名称'
,sender_name                   string            comment      '寄件人姓名'
,sender_mobile_phone           string            comment      '寄件人手机号'
,sender_province_id            bigint            comment      '寄件省份id'
,sender_province_name          string            comment      '寄件省份名称'
,sender_city_id                bigint            comment      '寄件城市id'
,sender_city_name              string            comment      '寄件城市名称'
,sender_area_id                bigint            comment      '寄件区域id'
,sender_area_name              string            comment      '寄件区域名称'
,sender_township               string            comment      '寄件乡镇'
,sender_street                 string            comment      '寄件街道'
,sender_detailed_address       string            comment      '寄件详细地址'
,origin_id                     bigint            comment      '始发地id'
,origin_name                   string            comment      '始发地名字'
,pick_network_code             string            comment      '寄件网点code'
,pick_network_name             string            comment      '寄件网点名称'
,pick_franchisee_code          string            comment      '寄件网点加盟商code'
,pick_franchisee_name          string            comment      '寄件网点加盟商名称'
,pick_agent_code               string            comment      '寄件网点代理区code'
,pick_agent_name               string            comment      '寄件网点代理区名称'
,freight                       decimal(14,2)     comment      '运费'
,total_freight                 decimal(14,2)     comment      '总运费'
,pick_finance_code             string            comment      '寄件财务中心code'
,dispatch_finance_code         string            comment      '派件财务中心code'
,receiver_name                 string            comment      '收件人姓名'
,receiver_mobile_phone         string            comment      '收件人手机号'
,receiver_province_id          bigint            comment      '收件省份id'
,receiver_province_name        string            comment      '收件省份名称'
,receiver_city_id              bigint            comment      '收件城市id'
,receiver_city_name            string            comment      '收件城市名称'
,receiver_area_id              bigint            comment      '收件区域id'
,receiver_area_name            string            comment      '收件区域名称'
,receiver_township             string            comment      '收件乡镇'
,receiver_street               string            comment      '收件街道'
,receiver_detailed_address     string            comment      '收件详细地址'
,receiver_postal_code          string            comment      '收件邮编'
,destination_id                bigint            comment      '目的地id'
,destination_name              string            comment      '目的地名称'
,receiver_sorting_code         string            comment      '收件分拣码'
,waybill_input_time            timestamp         comment      '录入时间'
,input_network_code            string            comment      '录入网点code'
,settlement_weight             decimal(14,2)     comment      '结算重量'
,customer_id                   bigint            comment      '客户id'
,last_update_time              timestamp         comment      '运单最后更新时间'
,is_delete                     smallint          comment      '是否删除1未删除,2已删除'
,is_plaintext                  tinyint           comment      ''
,real_name                     string            comment      '实名姓名'
,id_no_type                    bigint            comment      '证件类型'
,sex                           bigint            comment      '性别'
,distribute_finance_code       string            comment      '转运中心财务网点编码'
,package_collect_weight        decimal(14,2)     comment      '收件重量单位千克'
,package_receipt_weight        decimal(14,2)     comment      '入仓重量单位千克'
,package_arrival_weight        decimal(14,2)     comment      '集散到件重量单位千克'
,package_inside_charge_weight  decimal(14,2)     comment      '内部计费重量单位千克'
,is_distribute_scan            bigint            comment      '转运中心扫描标识:1=已转运,0=未转运,默认0'
,is_arrival_scan               bigint            comment      '集散到件标识:1=是,0=否,默认0'
,waybill_weight                decimal(14,2)     comment      '运单重量单位千克'
,is_business                   smallint          comment      '是否是商务件：0否,1是'
,is_receive                    smallint          comment      '是否已收款1是,0否'
,customer_network_code         string            comment      '客户网点code'
,order_id                      bigint            comment      '订单编号(订单表)'
,customer_order_id             string            comment      '客户订单编号(订单表)'
,order_source_code             string            comment      '订单来源code(订单表)'
,order_source_name             string            comment      '订单来源名称(订单表)'
,order_type_code               bigint            comment      '订单类型code(订单表)'
,packate_volume                decimal(9,2)      comment      '包裹体积重单位立方厘米(订单表)'
,customer_order_time           timestamp         comment      '客户下单时间(订单表)'
,order_input_time              timestamp         comment      '订单录入时间(订单表)'
,order_status_code             bigint            comment      '订单状态code(订单表)'
,cancel_time                   timestamp         comment      '订单取消时间(订单表)'
,real_pick_network_code        string            comment      '实际取件网点code(订单表)'
,dispatch_network_time         timestamp         comment      '调度网点时间(订单表)'
,pick_time                     timestamp         comment      '取件时间(订单表)'
,assigner_name                 string            comment      '委托人姓名(订单表)'
,assigner_mobile_phone         string            comment      '委托人手机号(订单表)'
,order_update_time             timestamp         comment      '订单更新时间(订单表)'
,create_by_code                string            comment      '创建人编码(订单表)'
,create_by_name                string            comment      '创建人姓名(订单表)'
,proxy_area_code               string            comment      '代理区code(订单表)'
,proxy_area_name               string            comment      '代理区name(订单表)'
,cancel_explain                string            comment      '取消订单说明(订单表)'
,customer_waybill_no           string            comment      '客户运单号(订单表)'
,sign_receipt                  tinyint           comment      '签回单 0:否 ,1:是，2:回单(订单表)'
,receipt_waybill_no            string            comment      '回单运单号(订单表)'
,is_transfer                   tinyint           comment      '是否转寄(1否,2是)(订单表)'
,order_pick_network_code       string            comment      '寄件网点code(订单表)'
,order_dispatch_network_code   string            comment      '派件网点code(订单表)'
,end_code                      string            comment      '完结类型编码(tab_end_piece表)'
,end_code_desc                 string            comment      '完结件编码描述(tab_end_piece表)'
,end_time                      timestamp         comment      '完结时间(tab_end_piece表)'
,end_network_code              string            comment      '完结扫描网点(tab_end_piece表)'
,end_source                    tinyint           comment      '来源 1、问题件 2、质量工单 3、仲裁 4、客服工单(tab_end_piece表)'
,order_source                  string            comment      '订单来源(tab_statistics表)'
)
    comment  '单证域主题宽表'
    partitioned by (dt string   comment  '时间分区')
    stored as parquet
    location '/dw/hive/jms_dwd.db/external/dwd_waybill_order_index_dt'
    tblproperties (
      'parquet.column.index.access'= 'true'
) ;
